--登录操作 使用账号密码登录后显示操作者姓名获取操作者在系统中的唯一标识符
select e.uuid,e.firstname,e.lastname from login l inner  join emp e on 1 l.eid = e.id; 
select e.uuid,e.firstname,e.lastname from login l inner  join emp e on 1 and l.account = 'hubin' and `password` = '1f4a79344c3e1f58' and l.eid = e.id ; 

--登录操作频繁使用
--把经常使用的查询的结果查询出来后放在一张虚表内 这张虚表我能称之为视图
create view person_id_card as 
 SELECT id,date(SUBSTR(id_card,7,8)) as birthday,MOD(SUBSTRING(`id_card`,17,1),2) AS gender FROM person;

select  p.id,p.uuid ,p.mobile ,p.password,p.first_name,p.last_name,p.id_card,p.address,p.head_img,p.role,p.access_token,ic.birthday,ic.gender 
 from person p left join person_id_card ic
 on p.id = ic.id;

create view person_id_card as
 select id as cid,date(substr(id_card,7,8)) birthday,mod(substr(id_card,17,1),2) gender
 from person ;

select id,uuid,mobile,password,first_name,last_name,id_card,address,head_img,role,access_token,birthday,gender 
from person left join person_id_card
 on id = cid;

drop view person_id_card;

create view emp_login as select l.account,l.password,e.uuid,e.firstname,e.lastname from login l inner  join emp e on l.eid = e.id; 
desc emp_login;
select * from emp_login;
select firstname,lastname from emp where uuid = '49c06e931d71634ac914d39a2a7ea90cdc50b41d88e78';
update emp_login set firstname = '呼呼' where uuid = '49c06e931d71634ac914d39a2a7ea90cdc50b41d88e78';
select firstname,lastname from emp where uuid = '49c06e931d71634ac914d39a2a7ea90cdc50b41d88e78';
update emp set lastname = '呼呼' where uuid = '49c06e931d71634ac914d39a2a7ea90cdc50b41d88e78';
select firstname,lastname from emp where uuid = '49c06e931d71634ac914d39a2a7ea90cdc50b41d88e78';
select * from emp_login;


--视图
select * from information_schema.views;

create view emp_view as select uuid,nickname from emp;

select * from emp_view;

update emp set nickname = 'lijie' where uuid = 'b8b1b87c2de305a3274d0583fbbd118bfb21191b85648';

select * from emp_view;

update emp_view set nickname = '李杰' where uuid = 'b8b1b87c2de305a3274d0583fbbd118bfb21191b85648';

--事务
--若干条sql当做一个整体 一条sql执行失败则整体执行失败 只有当所有sql执行成功才算成功
--MYSQL 事务处理主要有三种方法：
--
--1、用 BEGIN, ROLLBACK, COMMIT来实现

--BEGIN 开始一个事务
--ROLLBACK 事务回滚
--COMMIT 事务确认

--2、用 START TRANSACTION, ROLLBACK, COMMIT来实现

--START TRANSACTION 开始一个事务
--ROLLBACK 事务回滚
--COMMIT 事务确认
--3、直接用 SET 来改变 MySQL 的自动提交模式:

--SET AUTOCOMMIT=0 禁止自动提交
--SET AUTOCOMMIT=1 开启自动提交


--
create table atm (

 id int(11) auto_increment,

 nickname varchar(50) not null,

 money int(11) not null,

 primary key(id)

 );

 insert into atm (nickname,money) values ('孙敬轩',10000),('李朝蓬',10000);

 begin;

 update atm set money = (money - 100) where nickname = '孙敬轩';

 update atm set money = (money + 100) where nickname = '李朝蓬';

 commit;

 begin;

 update atm set money = (money - 100) where nickname = '孙敬轩';

 update atm set money = (money + 100) where nickname = '李朝蓬';

 savepoint zq01;

 update atm set money = (money - 100) where nickname = '孙敬轩';

 update atm set money = (money + 100) where nickname = '李朝蓬';

 savepoint zq02;

 update atm set money = (money - 100) where nickname = '孙敬轩';

 update atm set money = (money + 100) where nickname = '李朝蓬';

 savepoint zq03;

 

 rollback to zq02;

 rollback;


